package com.dalei.vds.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class LogDatabaseHelper extends SQLiteOpenHelper {
	
	public static final String DATABASE_NAME = "VDSLog";
	
	public static final String TABLE_LOG = "Log";
	
	public static final String TABLE_VEHICLE_INFORMATION = "VehicleInformation";
	
	public static final int DEFAULT_VERSION = 1;
	
	public LogDatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		// TODO Auto-generated constructor stub
		super(context, name, factory, version);
	}
	
	public LogDatabaseHelper(Context context) {
		// TODO Auto-generated constructor stub
		super(context, DATABASE_NAME, null, DEFAULT_VERSION);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		//Create table Vehicle Information
		String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_VEHICLE_INFORMATION +
				"(" +
				"_id INTEGER PRIMARY KEY, " +
				"_licensePlateNumber TEXT NOT NULL, " +
				"_licensePlateType INTEGER, " +
				"_vin TEXT, " +
				"_vehicleType INTEGER NOT NULL, " +
				"_brandAndModel TEXT, " +
				"_bodyColor INTEGER, " +
				"_engineModel TEXT, " +
				"_fuelType INTEGER, " +
				"_owners TEXT, " +
				"_registrationDate TEXT " +
				")";
		db.execSQL(sql);
		Log.i("LogDatabase", "Create table VEHICLE INFORMATION!");
		
		// Enable FOREIGN KEY On database
		sql = "PRAGMA foreign_keys = ON";
		db.execSQL(sql);
		Log.i("LogDatabase", "Enable FOREIGN KEY on database");
				
		// Create table Log
		sql = "CREATE TABLE IF NOT EXISTS " + TABLE_LOG +
				"( " +
				"_fid INTEGER PRIMARY KEY, " +
				"_vehicleInformationId INTEGER NOT NULL, " + 
				"_worker TEXT NOT NULL, " +
				"_timeStamp TEXT NOT NULL, " +
				"FOREIGN KEY(_vehicleInformationId) REFERENCES " + TABLE_VEHICLE_INFORMATION + "(_id) " +
				")";
		db.execSQL(sql);
		Log.i("LogDatabase", "Create table LOG!");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		if (oldVersion == db.getVersion()) {
			if (newVersion > oldVersion) {
				// Drop table LOG
				String sql = "DROP TABLE IF EXISTSS " + TABLE_LOG;
				db.execSQL(sql);
				Log.i("LogDatabase", "Drop table LOG!");
				
				// Drop table VEHICLE INFORMATION
				sql = "DROP TABLE IF EXISTSS " + TABLE_VEHICLE_INFORMATION;
				db.execSQL(sql);
				Log.i("LogDatabase", "Drop table VEHICLE INFORMATION!");
				
				onCreate(db);
				db.setVersion(newVersion);
			} else {
				Log.e("LogDatabase", "New version must be greater than old version!");
			}
		} else {
			Log.e("LogDatabase", "Old version don't match this database!");
		}
	}
}
